﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.talent.dao.impl;

import static com.hh.bss.util.ObjectUtils.isNotEmpty;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.hh.bss.auth.util.AuthSqlUtil;
import com.hh.bss.base.BaseHibernateDao;
import com.hh.bss.common.ConstantDic;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.Page;
import com.hh.bss.customer.vo.query.CustomerContacterQuery;
import com.hh.bss.project.entity.SearchProjectCv;
import com.hh.bss.talent.dao.CvDao;
import com.hh.bss.talent.entity.Cv;
import com.hh.bss.talent.entity.CvContactDetail;
import com.hh.bss.talent.vo.query.CvCustomerContacterQuery;
import com.hh.bss.talent.vo.query.CvQuery;
import com.hh.bss.talent.vo.query.CvSuccessQuery;
import com.hh.bss.util.BeanUtils;
import com.hh.bss.util.DateHelper;
import com.hh.bss.util.StringHelper;

@Repository
public class CvDaoImpl extends BaseHibernateDao<Cv, java.lang.Integer> implements CvDao<Cv, java.lang.Integer> {
	static Logger log = Logger.getLogger(CvDaoImpl.class);

	public Class getEntityClass() {
		return Cv.class;
	}

	public Page findPage(CvQuery query) {
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { CvQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		String mainSql = "";
		if (isNotEmpty(query.getComment())) {
			mainSql = "select t from Cv as  t , CvTag as c where t.id = c.cvId ";
		} else {
			mainSql = "select t from Cv t where 1=1 ";
		}
		StringBuilder sql2 = new StringBuilder(mainSql);
		if (isNotEmpty(query.getId())) {
			sql2.append(" and  t.id = :id ");
		}

		System.out.println(query.getCvName() + "*******************");

		if (isNotEmpty(query.getCvName())) {
			sql2.append(" and  t.cvName like :cvName ");
			query.setCvName("%" + query.getCvName() + "%");

		}
		if (isNotEmpty(query.getCvNameEn())) {
			sql2.append(" and  t.cvNameEn like :cvNameEn ");
			query.setCvNameEn("%" + query.getCvNameEn() + "%");

		}
		if (isNotEmpty(query.getNameInformer())) {
			sql2.append(" and  t.nameInformer like :nameInformer ");
			query.setNameInformer("%" + query.getNameInformer() + "%");

		}
		if (isNotEmpty(query.getNameDtBegin())) {
			sql2.append(" and  t.nameDt >= :nameDtBegin ");
		}
		if (isNotEmpty(query.getNameDtEnd())) {
			sql2.append(" and  t.nameDt <= :nameDtEnd ");
		}
		if (isNotEmpty(query.getSex())) {
			sql2.append(" and  t.sex like :sex ");
			query.setSex("%" + query.getSex() + "%");

		}
		if (isNotEmpty(query.getBirthdayBegin())) {
			sql2.append(" and  t.birthday >= :birthdayBegin ");
		}
		if (isNotEmpty(query.getBirthdayEnd())) {
			sql2.append(" and  t.birthday <= :birthdayEnd ");
		}
		if (isNotEmpty(query.getMobile())) {
			sql2.append(" and  t.mobile like :mobile ");
			query.setMobile("%" + query.getMobile() + "%");

		}
		if (isNotEmpty(query.getTel())) {
			sql2.append(" and  t.tel like :tel ");
			query.setTel("%" + query.getTel() + "%");

		}
		if (isNotEmpty(query.getIm())) {
			sql2.append(" and  t.im like :im ");
			query.setIm("%" + query.getIm() + "%");

		}
		if (isNotEmpty(query.getEmail())) {
			sql2.append(" and  t.email like :email ");
			query.setEmail("%" + query.getEmail() + "%");

		}
		if (isNotEmpty(query.getCompany())) {
			sql2.append(" and  t.company like :company ");
			query.setCompany("%" + query.getCompany() + "%");

		}
		if (isNotEmpty(query.getDept())) {
			sql2.append(" and  t.dept like :dept ");
			query.setDept("%" + query.getDept() + "%");

		}
		if (isNotEmpty(query.getTitle())) {
			sql2.append(" and  t.title like :title ");
			query.setTitle("%" + query.getTitle() + "%");

		}
		if (isNotEmpty(query.getIndustry())) {
			sql2.append(" and  t.industry = :industry ");
		}
		if (isNotEmpty(query.getFunction())) {
			sql2.append(" and  t.function = :function ");
		}
		if (isNotEmpty(query.getLevel())) {
			sql2.append(" and  t.level = :level ");
		}
		if (isNotEmpty(query.getMarried())) {
			sql2.append(" and  t.married = :married ");
		}
		if (isNotEmpty(query.getRegisterLocation())) {
			sql2.append(" and  t.registerLocation = :registerLocation ");
		}
		if (isNotEmpty(query.getAddress())) {
			sql2.append(" and  t.address like :address ");
			query.setAddress("%" + query.getAddress() + "%");

		}
		if (isNotEmpty(query.getPostCode())) {
			sql2.append(" and  t.postCode like :postCode ");
			query.setPostCode("%" + query.getPostCode() + "%");

		}
		if (isNotEmpty(query.getDegree())) {
			sql2.append(" and  t.degree = :degree ");
		}
		if (isNotEmpty(query.getWorkYear())) {
			sql2.append(" and  t.workYear = :workYear ");
		}
		if (isNotEmpty(query.getHomeBack())) {
			sql2.append(" and  t.homeBack like :homeBack ");
			query.setHomeBack("%" + query.getHomeBack() + "%");

		}
		if (isNotEmpty(query.getInterestEn())) {
			sql2.append(" and  t.interestEn like :interestEn ");
			query.setInterestEn("%" + query.getInterestEn() + "%");

		}
		if (isNotEmpty(query.getInterest())) {
			sql2.append(" and  t.interest like :interest ");
			query.setInterest("%" + query.getInterest() + "%");

		}
		if (isNotEmpty(query.getExpectLocation())) {
			sql2.append(" and  t.expectLocation = :expectLocation ");
		}
		if (isNotEmpty(query.getResumeInformer())) {
			sql2.append(" and  t.resumeInformer like :resumeInformer ");
			query.setResumeInformer("%" + query.getResumeInformer() + "%");

		}
		if (isNotEmpty(query.getResumeDtBegin())) {
			sql2.append(" and  t.resumeDt >= :resumeDtBegin ");
		}
		if (isNotEmpty(query.getResumeDtEnd())) {
			sql2.append(" and  t.resumeDt <= :resumeDtEnd ");
		}
		if (isNotEmpty(query.getCvType())) {
			sql2.append(" and  t.cvType like :cvType ");
			query.setCvType("%" + query.getCvType() + "%");

		}
		if (isNotEmpty(query.getCvStatus())) {
			sql2.append(" and  t.cvStatus like :cvStatus ");
			query.setCvStatus("%" + query.getCvStatus() + "%");

		}
		if (isNotEmpty(query.getCvFrom())) {
			sql2.append(" and  t.cvFrom like :cvFrom ");
			query.setCvFrom("%" + query.getCvFrom() + "%");

		}
		if (isNotEmpty(query.getCreator())) {
			sql2.append(" and  t.creator like :creator ");
			query.setCreator("%" + query.getCreator() + "%");

		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql2.append(" and  t.createDt >= :createDtBegin ");
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql2.append(" and  t.createDt <= :createDtEnd ");
		}
		if (isNotEmpty(query.getModifier())) {
			sql2.append(" and  t.modifier like :modifier ");
			query.setModifier("%" + query.getModifier() + "%");

		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			sql2.append(" and  t.modifiedDt >= :modifiedDtBegin ");
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			sql2.append(" and  t.modifiedDt <= :modifiedDtEnd ");
		}
		if (isNotEmpty(query.getHomeBackEn())) {
			sql2.append(" and  t.homeBackEn like :homeBackEn ");
			query.setHomeBackEn("%" + query.getHomeBackEn() + "%");

		}

		if (isNotEmpty(query.getComment())) {
			sql2.append(" and  c.comment like :comment ");
			query.setCvName("%" + query.getComment().trim() + "%");
		}

		sql2.append(" order by t.id desc ");
		/*
		 * if(isNotEmpty(query.getSortColumns())) {
		 * sql2.append(" order by :sortColumns "); }
		 */
		return pageQuery(sql2.toString(), query);
	}

	public Page findCvPage(CvQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();

		sql.append(" select * from ( "); // select
											// ROUND(datediff(CURRENT_DATE(),'2011-07-22')/365)
											// as a;
		sql.append(" select a.id,a.cv_name cvName,a.cv_name_en cvNameEn,a.name_informer nameInformer,a.name_dt nameDt,a.name_dt nameDtString,a.sex,a.birthday,a.birthday birthdayString ,a.company,a.dept,a.title,a.industry,a.function,a.integrity, ");
		sql.append(" a.level,a.married,a.register_location registerLocation ,a.address,a.post_code postCode,a.degree,a.work_year workYear ,a.expect_location expectLocation ");
		sql.append(" ,a.resume_informer resumeInformer,a.resume_creator resumeCreator,a.resume_dt resumeDt,a.resume_dt resumeDtString,a.cv_type cvType,a.cv_tag cvTag,a.cv_fl_level cvFlLevel,a.nationality,a.cv_status cvStatus,a.cv_from cvFrom,a.creator,a.modifier,a.create_dt createDt,a.create_dt createDtString,a.modified_dt modifiedDt,a.modified_dt modifiedDtString,a.logo,a.oldcompany,a.current_address currentAddress,e.current_salary currentSalary ");
		sql.append(" ,a.mobile,a.email,a.tel,a.company_id companyId,'' custId,'' contractId ,a.position,a.is_delete isDelete ,if(a.modified_dt<>null,a.modified_dt,a.create_dt) bothDt ");
		sql.append(" ,if(a.cv_text <> '',1,0) cvText   ");
		sql.append("  from cv a  ");
		sql.append(" left join cv_salary e on a.id=e.cv_id ");
		sql.append(" left join system_dic_map f on a.industry=f.key_id ");
		sql.append(" left join system_dic g  on a.function = g.id ");

		// 是否有附件
		// sql.append(" left join (select id,cv_id cvId  from cv_attachment  group by cvId ) x1 on x1.cvId=a.id ");
		// 是否被推荐过
		// sql.append(" left join (select id,cv_id cvId, from contract_job_cv_recom t1  ");

		sql.append(" where 1=1 ");
		// 申请删除 和 SC 要求删除 不显示
		sql.append(" and ifnull(a.integrity,0) not in ('9','-1') ");
		if (isNotEmpty(query.getId())) {
			// 多个Id 一起查询
			sql.append(" and  find_in_set(a.id,?) ");
			values.add(query.getId());
		}
		// 中英文名
		if (isNotEmpty(query.getBothName())) {
			sql.append(" and (a.cv_name like ? or  a.cv_name_en like ? )");
			values.add("%" + query.getBothName() + "%");
			values.add("%" + query.getBothName() + "%");
		}
		// 职位岗位
		if (isNotEmpty(query.getBothTitle())) {
			sql.append(" and (a.position like ? or  a.title like ? )");
			values.add("%" + query.getBothTitle() + "%");
			values.add("%" + query.getBothTitle() + "%");
		}
		// 联系方式
		if (isNotEmpty(query.getContactDetails())) {
			sql.append(" and exists ( select 1 from cv_contact_detail a1 where a1.cv_id=a.id and  a1.is_delete = 0 and a1.nbr like ? )");
			values.add("%" + query.getContactDetails() + "%");
		}
		if (isNotEmpty(query.getIntegrity())) {
			sql.append(" and a.integrity = ? ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getIsDelete())) {
			sql.append(" and a.is_delete=1 ");
		} else
			sql.append(" and a.is_delete=0 ");

		if (isNotEmpty(query.getCvName())) {
			sql.append(" and a.cv_name like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getCvNameEn())) {
			sql.append(" and a.cv_name_en like ? ");
			values.add("%" + query.getCvNameEn() + "%");
		}
		if (isNotEmpty(query.getNameInformer())) {
			sql.append(" and a.name_informer like ? ");
			values.add("%" + query.getNameInformer() + "%");
		}

		if (isNotEmpty(query.getOldcompanyNull())) {
			sql.append(" and ifnull(a.oldcompany,'')='' ");
		} else if (isNotEmpty(query.getOldcompany())) {
			sql.append(" and a.oldcompany like ? ");
			values.add("%" + query.getOldcompany() + "%");
		}

		if (isNotEmpty(query.getCompanyNull())) {
			sql.append(" and ifnull(a.company,'')='' ");
		} else if (isNotEmpty(query.getCompany())) {
			sql.append(" and a.company like ? ");
			values.add("%" + query.getCompany() + "%");
		}

		if (isNotEmpty(query.getPositionNull())) {
			sql.append(" and ifnull(a.position,'')='' ");
		} else if (isNotEmpty(query.getPosition())) {
			sql.append(" and a.position like ? ");
			values.add("%" + query.getPosition() + "%");
		}
		if (isNotEmpty(query.getTitleNull())) {
			sql.append(" and ifnull(a.title,'')='' ");
		} else if (isNotEmpty(query.getTitle())) {
			sql.append(" and a.title like ? ");
			values.add("%" + query.getTitle() + "%");
		}
		if (isNotEmpty(query.getSex())) {
			sql.append(" and  a.sex = ? ");
			values.add(query.getSex());
		}
		// 家庭地址
		if (isNotEmpty(query.getAddress())) {
			// address
			sql.append(" and a.address like ? ");
			values.add("%" + query.getAddress() + "%");
		}
		// 创建人 or 修改人
		if (isNotEmpty(query.getBothCreator())) {
			sql.append(" and  (a.creator = ?  or a.modifier = ? )");
			values.add(query.getBothCreator());
			values.add(query.getBothCreator());
		}
		if (isNotEmpty(query.getBothDtBegin()) && !isNotEmpty(query.getBothDtEnd())) {
			sql.append(" and  (a.create_dt > ? or a.modified_dt > ? ) ");
			values.add(query.getBothDtBegin());
			values.add(query.getBothDtBegin());

		} else if (!isNotEmpty(query.getBothDtBegin()) && isNotEmpty(query.getBothDtEnd())) {
			sql.append(" and  (a.create_dt < ? or a.modified_dt < ? ) ");
			values.add(DateHelper.add(query.getBothDtEnd(), DateHelper.DAY, 1));
			values.add(DateHelper.add(query.getBothDtEnd(), DateHelper.DAY, 1));
			// logger.info("查询时间:"+query.getBothDtEnd());
		} else if (isNotEmpty(query.getBothDtBegin()) && isNotEmpty(query.getBothDtEnd())) {
			sql.append(" and ( a.create_dt > ?  and  a.create_dt <  ?    or  a.modified_dt > ?  and  a.modified_dt <  ?   ) ");
			values.add(query.getBothDtBegin());
			values.add(query.getBothDtEnd());
			// values.add(DateHelper.add(query.getBothDtEnd(), DateHelper.DAY,
			// 1));

			values.add(query.getBothDtBegin());
			values.add(query.getBothDtEnd());
			// values.add(DateHelper.add(query.getBothDtEnd(), DateHelper.DAY,
			// 1));
			// logger.info("查询时间222222:"+query.getBothDtEnd());
		}

		// 关键字
		// 教育经历、培训经历、资格证书、工作经历、附件。
		if (isNotEmpty(query.getKeyword())) {
			// sql.append(" and  a.cv_text like  ? ");
			sql.append(" and  a.cv_text like  ?   ");
			values.add("%" + query.getKeyword() + "%");
		}

		// 人才仓库查询功能，把“行业”、“职能”、“职级”改成多选项。
		if (isNotEmpty(query.getIndustry())) {
			// String [] industryArr =
			// sql.append(" and  cvinfo.industry in (?)"); //二级行业 industry
			// /values.add(query.getIndustry());
			sql.append(" and  a.industry in (" + query.getIndustry() + ")");
		}
		if (isNotEmpty(query.getParentIndustry())) {
			sql.append(" and  exists( select 1 from system_dic a1 where a.industry = a1.id  and a1.category='INDUSTRY' and a1.parent_id= ? )");
			values.add(query.getParentIndustry());
		}
		if (isNotEmpty(query.getFunction())) {
			// sql.append(" and  cvinfo.function in (?) "); //function 二级职能
			// values.add(query.getFunction());
			sql.append(" and  a.function in (" + query.getFunction() + ") "); // function
																				// 二级职能
		}
		if (isNotEmpty(query.getExpectLocation())) {
			sql.append(" and  a.expect_location = ? ");
			values.add(query.getExpectLocation());
		}
		if (isNotEmpty(query.getParentFunction())) { // 一级职能
			sql.append(" and  exists( select 1 from system_dic a where a.function = a.id  and a.category='JOB' and a.parent_id = ? ) ");
			values.add(query.getParentFunction());
		}

		if (isNotEmpty(query.getLinkage())) {
			if (query.getLinkage() == 1) {
				sql.append("and (not find_in_set(g.parent_id,f.key_map_ids) or a.industry is null ) ");
			}
		}
		sql.append(" ) cvinfo where 1=1");

		if (isNotEmpty(query.getCurrentSalary())) {
			sql.append(" and  cvinfo.currentSalary = ? ");
			values.add(query.getCurrentSalary());
		}
		if (isNotEmpty(query.getLevel())) {
			// sql.append(" and  cvinfo.level = ? ");
			// values.add(query.getLevel());
			sql.append(" and  cvinfo.level in (" + query.getLevel() + ") ");
		}

		if (isNotEmpty(query.getDegree())) {
			sql.append(" and  cvinfo.degree = ? ");
			values.add(query.getDegree());
		}
		if (isNotEmpty(query.getCurrentAddress())) {
			sql.append(" and  cvinfo.currentAddress like ? ");
			values.add("%" + query.getCurrentAddress() + "%");

		}
		if (isNotEmpty(query.getComment())) {
			sql.append(" and  exists( select 1 from cv_tag a where a.cv_id=cvinfo.id and (a.tag like ? or a.comment like ?) )");
			values.add("%" + query.getComment() + "%");
			values.add("%" + query.getComment() + "%");
		}
		if (isNotEmpty(query.getSkill())) {
			sql.append(" and  exists( select 1 from cv_skill a where a.cv_id=cvinfo.id and (a.skill like ? or a.skill_en like ? or a.level like ? or a.level_en like ?) )");
			values.add("%" + query.getSkill() + "%");
			values.add("%" + query.getSkill() + "%");
			values.add("%" + query.getSkill() + "%");
			values.add("%" + query.getSkill() + "%");
		}
		if (isNotEmpty(query.getIsProtectCv())) {
			sql.append(" and  not exists(select 1 from  customer a,customer_contacter b where a.id=b.cust_id and b.cv_id=cvinfo.id) ");
		}

		// ------------------------

		if (isNotEmpty(query.getPhone())) {
			sql.append(" and exists ( select 1 from cv_contact_detail a where a.is_delete = 0 and a.nbr like ? )");
			// sql.append(" and ( cvinfo.tel like ?  ) ");
			values.add("%" + query.getPhone() + "%");
		}
		if (isNotEmpty(query.getMobile())) {
			// sql.append(" and ( cvinfo.mobile like ? ) ");
			sql.append(" and exists ( select 1 from cv_contact_detail a where a.is_delete = 0 and a.cv_id = cvinfo.id and a.nbr like ? )");
			values.add("%" + query.getMobile() + "%");
		}
		if (isNotEmpty(query.getEmail())) {
			// sql.append(" and cvinfo.email like ? ");
			sql.append(" and exists ( select 1 from cv_contact_detail a where a.is_delete = 0 and a.cv_id = cvinfo.id and a.nbr like ? )");
			values.add("%" + query.getEmail() + "%");
		}
		if (isNotEmpty(query.getCvStatus())) {
			sql.append(" and cvinfo.cvStatus = 'F' ");
		}
		if (isNotEmpty(query.getCvType())) {
			sql.append(" and cvinfo.cvType = ? ");
			values.add(query.getCvType());
		}
		if (isNotEmpty(query.getCvTag())) {
			sql.append(" and cvinfo.cvTag = ? ");
			values.add(query.getCvTag());
		}

		// 创建人
		if (isNotEmpty(query.getCreator())) {
			sql.append(" and  cvinfo.creator = ? ");
			values.add(query.getCreator());
		}
		if (isNotEmpty(query.getCreateDtBegin())) {
			sql.append(" and  cvinfo.createDt > ? ");
			values.add(query.getCreateDtBegin());
		}
		if (isNotEmpty(query.getCreateDtEnd())) {
			sql.append(" and  cvinfo.createDt < ? ");
			// values.add(query.getCreateDtEnd());
			values.add(DateHelper.add(query.getCreateDtEnd(), DateHelper.DAY, 1));
		}
		if (isNotEmpty(query.getModifier())) {
			// product
			sql.append(" and  cvinfo.modifier = ? ");
			values.add(query.getModifier());
		}
		if (isNotEmpty(query.getModifiedDtBegin())) {
			// product
			sql.append(" and  cvinfo.modifiedDt > ? ");
			values.add(query.getModifiedDtBegin());
		}
		if (isNotEmpty(query.getModifiedDtEnd())) {
			// product
			sql.append(" and  cvinfo.modifiedDt < ? ");
			// values.add(query.getModifiedDtEnd());
			values.add(DateHelper.add(query.getModifiedDtEnd(), DateHelper.DAY, 1));
		}

		if (isNotEmpty(query.getExintegrity())) {
			sql.append(" and   ifnull(cvinfo.integrity,0) not   in (").append(query.getExintegrity()).append(") ");
			// System.out.println("d*************"+sql.toString());
			// values.add(query.getExintegrity());
		}
		if (isNotEmpty(query.getHasAttachment())) {
			Integer attachement = query.getHasAttachment();
			if (attachement == -1) {
				sql.append(" and not exists (select 1 from cv_attachment a where a.cv_id = cvinfo.id ) ");
			} else if (attachement == 1) {
				sql.append(" and  exists (select 1 from cv_attachment a where a.cv_id = cvinfo.id ) ");
			}

		}
		/*
		 * //修改人 if(isNotEmpty(query.getModifier())){
		 * sql.append(" and cvinfo.modifier like ? ");
		 * values.add("%"+query.getModifier()+"%"); }
		 */

		// ,(datediff(current_date(),a.birthday)/365) ageRange
		// ,(substring(current_date(),1,4) - a.work_year) workRange
		if (isNotEmpty(query.getAgeRange())) { // 年龄范围
			// //年龄范围 ：1 20-29，2 30-39，3 40-49，4 50-59.
			int age = Integer.valueOf(query.getAgeRange());
			if (age == 1) {
				sql.append(" and (datediff(current_date(),cvinfo.birthday) /365)  between 20 and 29  ");
			} else if (age == 2) {
				sql.append(" and (datediff(current_date(),cvinfo.birthday) /365)  between 30 and 39  ");
			} else if (age == 3) {
				sql.append(" and (datediff(current_date(),cvinfo.birthday) /365)  between 40 and 49  ");
			} else if (age == 4) {
				sql.append(" (datediff(current_date(),cvinfo.birthday) /365)  between 50 and 59  ");
			}
		}

		if (isNotEmpty(query.getWorkRange())) { // 年龄范围
			// //年龄范围 ：-1 一年一下 1 1-2 2 3-5 3 6-7 4 8-10 5 (10-15) 6 (15 年一上)
			int age = Integer.valueOf(query.getWorkRange());
			if (age == -1) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)  < 1   ");
			} else if (age == 1) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)  between 1 and 2  ");
			} else if (age == 2) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)  between 3 and 5  ");
			} else if (age == 3) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)  between 6 and 7  ");
			} else if (age == 4) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)  between 8 and 10  ");
			} else if (age == 5) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)  between 10 and 15  ");
			} else if (age == 6) {
				sql.append(" and (substring(current_date(),1,4) - cvinfo.workYear)   > 15 ");
			}
		}
		if (isNotEmpty(query.getCompanyId())) {
			sql.append(" and cvinfo.companyId = ? ");
			values.add(query.getCompanyId());
		}
		// 错误的行业
		if (isNotEmpty(query.getErrIndustry())) {
			sql.append(" and not exists  (select 1 from system_dic b where b.category='INDUSTRY' and b.status = 'A' and b.parent_id <> 0 and cvinfo.industry = b.id )");
		}

		if (isNotEmpty(query.getSortColumns())) {
			sql.append("  order by cvinfo.").append(query.getSortColumns());
		} else {
			sql.append("  order by cvinfo.id desc ");
		}
		logger.info("人才仓库查询SQL" + sql);

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public Page findSimpleCvPage(CvQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();

		sql.append(" select * from ( "); // select
											// ROUND(datediff(CURRENT_DATE(),'2011-07-22')/365)
											// as a;
		sql.append(" select a.id,a.cv_name cvName,a.cv_name_en cvNameEn,a.company,a.dept,a.title,a.industry,a.function ");
		sql.append("  from cv a  ");

		 sql.append(" where 1=1 ");

		if (isNotEmpty(query.getId())) {
			// 多个Id 一起查询
			sql.append(" and  find_in_set(a.id,?) ");
			values.add(query.getId());
		}
		// 中英文名
		if (isNotEmpty(query.getBothName())) {
			sql.append(" and (a.cv_name like ? or  a.cv_name_en like ? )");
			values.add("%" + query.getBothName() + "%");
			values.add("%" + query.getBothName() + "%");
		}

		// 联系方式
		if (isNotEmpty(query.getContactDetails())) {
			sql.append(" and exists ( select 1 from cv_contact_detail a1 where a1.cv_id=a.id and  a1.is_delete = 0 and a1.nbr like ? )");
			values.add("%" + query.getContactDetails() + "%");
		}

		if (isNotEmpty(query.getKeyword())) {
			// sql.append(" and  a.cv_text like  ? ");
			sql.append(" and  a.cv_text like  ?   ");
			values.add("%" + query.getKeyword() + "%");
		}
		sql.append(" ) cvinfo where 1=1");

		if (isNotEmpty(query.getSortColumns())) {
			sql.append("  order by cvinfo.").append(query.getSortColumns());
		} else {
			sql.append("  order by cvinfo.id desc ");
		}
		logger.info("人才仓库查询SQL" + sql);

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	public void flush() {
		super.flush();
	}

	public Map getCvInfoById(Integer id) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.cv_name cvName,a.cv_name_en cvNameEn,a.sex,a.birthday,a.company,a.dept,a.title,a.industry,a.function,");
		sql.append(" a.level,a.married,a.register_location registerLocation,a.address,a.post_code postCode,a.degree,a.work_year workYear,a.expect_location expectLocation,a.logo,a.current_address currentAddress ");
		sql.append(" ,a.mobile,a.tel phone,e.current_salary currentSalary,a.email ");
		sql.append("  from cv a  ");
		sql.append(" left join cv_salary e on a.id=e.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.id=f.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.id=h.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.id=i.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.id=j.cv_id ");
		sql.append(" ) cvinfo where 1=1");

		sql.append(" and cvinfo.id = ? ");
		values.add(id);

		List l = super.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0)
			return (Map) l.get(0);
		else
			return new HashMap();

	}

	public Page findSuccessCvPage(CvSuccessQuery query) {
		// 人选姓名，职位，客户职位联系人,offer日期，上班日期，项目负责人，信息录入人，手机，电邮，客户公司 修改人 修改时间
		// ＝＝》招聘客户，录用职位，录用人选， 录用日期 英文名， 性别，行业，职能，工作年限，当前所在地， 手机，电邮
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.recom_id recomId,a.cv_id cvId,a.job_id jobId,b.cv_name cvName,b.cv_name_en cvNameEn,b.current_address currentAddress,b.mobile,b.tel phone,b.email,b.married,b.sex,b.industry,b.function,b.level,b.degree,b.work_year workYear,b.expect_location expectLocation,a.name jobName,a.onduty_d ondutyDt,a.apply_d applyD,b1.abbr_name custName,b1.name cnName,b1.full_name custFullName,a.creator,a.create_dt createDt,a.modifier,a.modifiedDt,a.contract_id ,h11.jobManager   from (select distinct a1.id,a1.recom_id,a1.creator,a1.create_dt,a1.onduty_d,a1.apply_d,a1.modifier,a1.modified_dt modifiedDt,a0.cv_id,a0.job_id,a2.name,a2.hr_contacter,a2.ar_contacter,a2.cust_id, a2.contract_id  from contract_job_cv_recom a0,contract_job_cv_offer a1,contract_job a2 where a0.id=a1.recom_id and a0.job_id=a2.id) a ");
		sql.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		// customer_contacter
		// contract_job_partner
		sql.append(" left join  ( select a11.job_id,a11.id, group_concat((b11.name) separator ',') as jobManager from contract_job_partner a11,system_user b11 where a11.account=b11.account and a11.role='").append(ConstantDic.JOB_PARTNER_TYPE_MC).append("' group by a11.job_id) h11 on h11.job_id=a.contract_id ");
		sql.append(" left join  ( select t2.id,t1.abbr_name,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) b1 on a.cust_id=b1.id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.cv_id=f.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.cv_id=h.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.cv_id=i.cv_id ");
		// sql.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.cv_id=j.cv_id ");
		sql.append(" ) comcv where 1=1 ");
		List values = new ArrayList();

		if (isNotEmpty(query.getCvName())) {
			sql.append(" and comcv.cvName like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getJobName())) {
			sql.append(" and comcv.jobName like ? ");
			values.add("%" + query.getJobName() + "%");
		}
		if (isNotEmpty(query.getMobile())) {
			sql.append(" and comcv.mobile like ? ");
			values.add("%" + query.getMobile() + "%");
		}
		if (isNotEmpty(query.getPhone())) {
			sql.append(" and comcv.phone like ? ");
			values.add("%" + query.getPhone() + "%");
		}
		if (isNotEmpty(query.getEmail())) {
			sql.append(" and comcv.email like ? ");
			values.add("%" + query.getEmail() + "%");
		}
		if (isNotEmpty(query.getCustName())) {
			sql.append(" and (comcv.custName like ?  or comcv.custFullName like ? or comcv.cnName like ? )");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
		}

		// jian
		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authJobSQL(query.getOnlineUser().getUsername(), "comcv.jobId", "t.creator");
			if (!StringHelper.isEmpty(authsql))
				sql.append(authsql);
		}
		// return super.getJdbcDao().createPage(query, values, mapper,
		// pageMaxSize, pageNumber)
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	/**
	 * 新增简历时 如果选择了现任公司 就把人加入到公司的组织架构图中
	 */

	public void saveCompanyTree(SearchProjectCv search) {
		String sql = "select id,dept_id from company_dept_title where company_id = '" + search.getCompanyId() + "'";
		List<Map<String, Object>> list = getJdbcDao().getJdbcTemplate().queryForList(sql);
		Integer titleId = (Integer) list.get(0).get("id");
		Integer deptId = (Integer) list.get(0).get("dept_id");
		search.setDeptId(deptId);
		search.setTitleId(titleId);
		this.getHibernateTemplate().save(search);
	}

	/**
	 * 根据公司名 获得公司id
	 */

	public Integer getCompanyId(String companyName) {
		String sql = "select id from company where name = '" + companyName + "'";
		Integer companyId = null;
		try {
			companyId = this.getJdbcDao().getJdbcTemplate().queryForInt(sql);
		} catch (Exception ex) {
		}
		return companyId;
	}

	/**
	 * 判断在Search_project_cv 表里是否存在记录
	 * 
	 * @param 简历Id
	 *            公司Id
	 */

	public boolean isExistSearchProjectCv(Integer cvId, String companyId) {
		String sql = "select count(*) from search_project_cv where cv_id ='" + cvId + "' and company_id ='" + companyId + "'";
		Integer count = getJdbcDao().getJdbcTemplate().queryForInt(sql);
		String sql2 = "select id,dept_id from company_dept_title where company_id = '" + companyId + "'";
		List list = getJdbcDao().getJdbcTemplate().queryForList(sql2);
		if (count == 0 && list.size() > 0) {
			return true;
		} else {
			return false;
		}

	}

	/**
	 * 判断用户是否存在
	 * 
	 * @author Alvin Liu 修改 2012年7月10日 15:49:43 更改判断条件 (姓名 + 生日) 2012年8月9日
	 *         15:27:48 改判断条件 只要 手机 or 邮件重复 人选 就重复.
	 */
	public boolean checkUser(Cv cv, List<CvContactDetail> contact_list) {
		// try {

		/*
		 * //this.getHibernateTemplate().find("from bean.User u where u.name=?",
		 * "test");
		 * 
		 * @SuppressWarnings("unchecked") List<Cv> cvList =
		 * this.getHibernateTemplate().find("from Cv cv where cv.cvName=?",
		 * cv.getCvName()); if(cvList.size()>0){ for(Cv cvs : cvList){
		 * 
		 * List<CvContactDetail> lists = this.getHibernateTemplate().find(
		 * "from CvContactDetail cd where cd.cvId=?", cvs.getId());
		 * if(lists.size()>0){ for(CvContactDetail cds : list){ //传过来的联系方式信息
		 * String ctType = cds.getContactType(); if("M".equals(ctType)){
		 * for(CvContactDetail cts : lists){ //数据库查询处出来的的联系方式信息
		 * if("M".equals(cts.getContactType())){
		 * if(cds.getNbr().equals(cts.getNbr())){ return true; } } } } }
		 * 
		 * }
		 * 
		 * } }
		 */
		// List<CvContactDetail> contactList =
		// this.getHibernateTemplate().find("from CvContactDetail cd where cd.cvId=?",
		// cv.getId());
		/*
		 * List<CvContactDetail> contactList =
		 * this.getHibernateTemplate().find("from CvContactDetail ");
		 * if(contactList.size()>0){ for(CvContactDetail cvContactDetail :
		 * cvList){ //传过来的联系方式信息 String ctType =
		 * cvContactDetail.getContactType(); if("M".equals(ctType) ||
		 * "E".equals(ctType)){ //手机 或者 邮件 for(CvContactDetail contact :
		 * contactList){ //数据库查询处出来的的联系方式信息
		 * if("M".equals(contact.getContactType()) ||
		 * "E".equals(contact.getContactType())){
		 * if(cvContactDetail.getNbr().equals(contact.getNbr())){ return true; }
		 * } } } } } } catch (Exception e) { e.printStackTrace(); } return
		 * false;
		 */

		List<Cv> cvList = this.getHibernateTemplate().find("from Cv cv where cv.cvName=? and  cv.isDelete  = 0 ", cv.getCvName()); // 判断人选是否存在
		// System.out.println("dfdsfdsfdsfds-000--------------"+cv.getCvName());
		if (cvList.size() > 0) {

			// CvContactDetail cd =
			// this.getHibernateTemplate()..("from Cv cv where cv.cvName=?",
			// cvName); //判断人选是否存在
			List<CvContactDetail> cdList = new ArrayList<CvContactDetail>();// this.getHibernateTemplate().find("from Cv cv where cv.cvName=?",
																			// cvName);
																			// //判断人选是否存在
			// 判断电话和手机/邮件
			for (Cv cvs : cvList) {
				Integer id = cvs.getId();
				cdList = this.getHibernateTemplate().find("from CvContactDetail cd where cd.isDelete= 0 and cd.cvId=?", id); // 判断人选是否存在

				for (CvContactDetail cds : cdList) {
					String contactType = cds.getContactType();

					// 如果是同一个cv id的则不处理
					if (cds.getCvId().equals(cv.getId())) {
						// System.out.println("dfdsfdsfdsfds---------------");
						continue;
					}

					for (CvContactDetail cvContact : contact_list) {

						if (contactType.equals(ConstantDic.ContactType_TEL) && cvContact.getContactType().equals(ConstantDic.ContactType_TEL)) {// 电话
							String nbrExt1 = cds.getNbrExt1(); // 区号
							String nbr = cds.getNbr(); // 电话号码
							String nbrExt2 = cds.getNbrExt2(); // 分机号
							String nbrExt1s = cvContact.getNbrExt1(); // 区号
							String nbrs = cvContact.getNbr(); // 电话号码
							String nbrExt2s = cvContact.getNbrExt2(); // 分机号
							String oldNbr = StringHelper.formatNullValue(nbrExt1, "") + StringHelper.formatNullValue(nbr, "") + StringHelper.formatNullValue(nbrExt2, "");
							String newNbr = StringHelper.formatNullValue(nbrExt1s, "") + StringHelper.formatNullValue(nbrs, "") + StringHelper.formatNullValue(nbrExt2s, "");
							if (oldNbr.equals(newNbr))
								return true;
							// if(nbrExt1.equals(nbrExt1) && nbr.equals(nbrs) &&
							// nbrExt2.equals(nbrExt2s)){
							// return true;
							// }
						} else if (contactType.equals(ConstantDic.ContactType_EMAIL) && cvContact.getContactType().equals(ConstantDic.ContactType_EMAIL)) {// Email
							String email = cds.getNbr();
							String email2 = cvContact.getNbr();
							if (StringUtils.isNotEmpty(email) && StringUtils.isNotEmpty(email2)) {
								if (email.equals(email2)) {
									return true;
								}
							}

						} else if (contactType.equals(ConstantDic.ContactType_MOBILE) && cvContact.getContactType().equals(ConstantDic.ContactType_MOBILE)) {// 手机
							String mob = cds.getNbr();
							String mobile = cvContact.getNbr();
							if (StringUtils.isNotEmpty(mob) && StringUtils.isNotEmpty(mobile)) {
								if (mob.equals(mobile)) {
									return true;
								}
							}

						}
					}
				}
			}
		} else {
			// System.out.println("没有人选啦---------------xxxxxxxxxxxxxxxxxxxxxxxxxxx");
		}
		return false;
	}

	public Page findCustomerContacterCvPage(CvCustomerContacterQuery query) {
		// TODO Auto-generated method stub
		// XsqlBuilder syntax,please see
		// http://code.google.com/p/rapid-xsqlbuilder
		// [column]为字符串拼接, {column}为使用占位符.
		// [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接
		// [column] 为PageRequest的属性

		Class[] os = { CustomerContacterQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}
		// 生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
		StringBuffer sql2 = new StringBuffer();
		List values = new ArrayList();
		sql2.append("select * from ( ");
		sql2.append(" select a.id,a.cust_id custId,d.abbr_name custName,d.name chName,d.full_name custFullName,a.role,a.cv_id cvId,b.cv_name as cvName,b.cv_name_en cvNameEn,b.current_address currentAddress,b.title ,a.comment,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt,e.current_salary currentSalary,b.mobile,b.tel phone,b.email,b.married,b.sex,b.industry,b.function,b.level,b.degree,b.work_year workYear,b.expect_location expectLocation  from customer_contacter a ");
		sql2.append(" left join  cv b on a.cv_id=b.id and b.is_delete=0 ");
		sql2.append(" left join  (select t2.id,t1.abbr_name,t1.name,t1.full_name from company t1,customer t2 where t1.id=t2.company_id ) d on a.cust_id=d.id ");
		sql2.append(" left join cv_salary e on a.cv_id=e.cv_id ");
		// sql2.append(" left join (select cv_id,group_concat((nbr) separator ',') as mobile from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_MOBILE).append("' group by cv_id) f on a.cv_id=f.cv_id ");
		// sql2.append(" left join (select cv_id,group_concat((concat(if(nbr_ext1 is null,'','('),ifnull(nbr_ext1,''),if(nbr_ext1 is null,'',')'),ifnull(nbr,''),if(nbr_ext2 is null,'','-'),ifnull(nbr_ext2,''))) separator ',') as phone from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_TEL).append("' group by cv_id) h on a.cv_id=h.cv_id ");
		// sql2.append(" left join (select cv_id,group_concat((nbr) separator ',') as email from cv_contact_detail where contact_type='").append(ConstantDic.ContactType_EMAIL).append("' group by cv_id) i on a.cv_id=i.cv_id ");
		// sql2.append(" left join (select cv_id,group_concat((tag) separator ',') as tag from cv_tag group by cv_id) j on a.cv_id=j.cv_id ");
		sql2.append(" ) t where 1=1 ");

		if (isNotEmpty(query.getCustRole())) {
			sql2.append(" and t.role=? ");
			values.add(query.getCustRole());
		}
		if (isNotEmpty(query.getCvName())) {
			sql2.append(" and t.cvName like ? ");
			values.add("%" + query.getCvName() + "%");
		}
		if (isNotEmpty(query.getMobile())) {
			sql2.append(" and t.mobile like ? ");
			values.add("%" + query.getMobile() + "%");
		}
		if (isNotEmpty(query.getPhone())) {
			sql2.append(" and t.phone like ? ");
			values.add("%" + query.getPhone() + "%");
		}
		if (isNotEmpty(query.getEmail())) {
			sql2.append(" and t.email like ? ");
			values.add("%" + query.getEmail() + "%");
		}
		if (isNotEmpty(query.getCustName())) {
			sql2.append(" and (t.custName like ? or t.chName like ?  or t.custFullName like ? )");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
			values.add("%" + query.getCustName() + "%");
		}
		System.out.println(sql2.toString());
		if (query.getOnlineUser() != null) {
			String authsql = AuthSqlUtil.authBDCustomerSQL(query.getOnlineUser().getUsername(), "t.custId", "t.creator");
			if (!StringHelper.isEmpty(authsql))
				sql2.append(authsql);
		}

		return super.getJdbcDao().createPage(sql2.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public Page findCvProjectPage(CvQuery query) {

		Class[] os = { CvQuery.class };
		Class property = org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
		if (property != null) {
			if ("java.lang.String".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), query.getQuery());

			} else if ("java.lang.Integer".equals(property.getName())) {

				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));

			} else if ("java.lang.Double".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
			} else if ("java.util.Date".equals(property.getName())) {
				com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
			}
		}

		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("select * from ( select a.id projectId,a.name,a.account from  search_project a  where 1=1 ");
		sql.append(" and  exists  (select 1 from search_project_cv a1 where 1=1 and a1.project_id = a.id  and a1.cv_id =  ?  ) ");
		sql.append(" ) t where 1=1 ");
		values.add(query.getId());
		logger.info("查询人才所在SL(SQL):" + sql.toString());
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public void importCvFromExcel(String fileName) {
		StringBuffer info = new StringBuffer();
		// 公司中文名 公司英文名 公司简称 公司性质 二级行业 机构类型 电话总机 网站 城市 人员规模 公司介绍-内部使用 传真 地址 邮编
		// 公司产品 行业排名 注册资本 资本描述 公司背景 公司文化 公司介绍-外部使用
		// Map paramsMap = new HashMap();
		// 公司中文名 公司英文名 公司简称 公司性质 二级行业 机构类型 电话总机 网站 城市 人员规模 公司介绍-内部使用 传真 地址 邮编
		// 公司产品 行业排名 注 册资本 资本描述 公司背景 公司文化 公司介绍-外部使用
		/**
		 * id cv_name cv_name_en name_informer name_dt sex birthday mobile tel
		 * im email company dept title industry function level married
		 * register_location address post_code degree work_year home_back
		 * interest_en interest expect_location resume_informer resume_dt
		 * cv_type cv_status cv_from base_creator creator create_dt modifier
		 * modified_dt home_back_en logo oldcompany current_address company_id
		 * resume_creator oldcompany_id cv_tag cv_fl_level nationality position
		 * integrity is_delete delete_dt cv_text
		 */
		// 中文名 英文名 性别 手机 电话（分机） 电子邮件 人选类别 人选标签 现任公司 现职位名称 职级 二级行业 一级职能 二级职能 所在省会
		// 所在城市 期望工作地 当前所在地 国籍 家庭地址 邮政编码 出生日期 学历 婚姻状况 外语能力 年薪范围 开始工作年份
		String[] params = { "cvName", "cvNameEn", "mobile", "tel", "email", "cvType", "cvTag", "company", "position", "level", "function", "industry", "registerLocation", "expectLocation", "currentAddress", "nationality", "address", "postCode", "birthday", "degree", "married", "cvFlLevel", "currentSalary", "workYear" };

		//
		try {
			InputStream inputStream = new FileInputStream(fileName);
			// 创建对Excel工作簿文件的引用
			HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
			// 创建对工作表的引用。
			HSSFSheet sheet = workbook.getSheetAt(0);
			// 遍历所有单元格，读取单元格
			for (int i = 1; i <= sheet.getLastRowNum(); i++) {
				HSSFRow row = sheet.getRow(i);
				if (row != null) {
					Cv cv = new Cv();
					cv.setCreateDt(new Date());
					cv.setCreator("waki.hu");
					for (int j = 0; j < row.getLastCellNum(); j++) {
						HSSFCell cell = row.getCell(j); // 获取每个单元格对象
						if (cell != null) {
							// 通过 row.getCell(j).toString() 获取单元格内容，
							if (BeanUtils.isExistsProperty(Cv.class, params[j])) {
								// BeanUtils.setProperty(company, params[j],
								// cell.toString());
								if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) { // 单元格内容为数字
									BeanUtils.setProperty(cv, params[j], cell.getNumericCellValue());
								} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
									BeanUtils.setProperty(cv, params[j], cell.getStringCellValue().toString());
								}

							}

						}
					}
					List companyList = this.findCvList(cv);
					if (companyList != null && companyList.size() > 0) {
						info.append("中/英 姓名:" + cv.getCvName());
						if (StringUtils.isNotEmpty(cv.getCvNameEn())) {
							info.append("/").append(cv.getCvNameEn());
						}
						info.append("\r\n");
						for (int k = 0; k < companyList.size(); k++) {
							Map map = (Map) companyList.get(k);
							String cvId = (String) map.get("cv_id");
							String nbr = (String) map.get("nbr");

							info.append("  重复人选Id:").append(map.get("cv_id"));
							if (StringUtils.isNotEmpty(nbr) && StringUtils.isNotEmpty(cv.getMobile())) {
								if (nbr.contains(cv.getMobile())) {
									info.append("  重复手机:" + nbr);
								}
							}
							if (StringUtils.isNotEmpty(nbr) && StringUtils.isNotEmpty(cv.getEmail())) {
								if (nbr.contains(cv.getEmail())) {
									info.append("  重复邮箱:" + nbr);
								}
							}
							info.append("\r\n");
						}
					} else {
						if (StringUtils.isNotEmpty(cv.getCvName())) {
							logger.info("公司:" + cv.getCvName() + "导入成功!");
							this.save(cv);
							this.flush();
							// 处理现任公司
							// this.
						}

					}
				}

			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		logger.info("========开始生成人选导入报告======");
		// OutputStream outputStream = new Out
		// WriterOutputStream outputStream = new WriterOutputStream(new
		// FileInputStream("C:\\import.txt"));
		BufferedWriter bufferedWriter = null;
		try {
			bufferedWriter = new BufferedWriter(new FileWriter("C:\\importCvReport.txt"));
			bufferedWriter.write(info.toString());
			bufferedWriter.flush();
			// bufferedWriter.close();
			logger.info("========生成人选导入报告结束======");
			logger.info("导入成功....");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (bufferedWriter != null) {
				try {
					bufferedWriter.close();
				} catch (Exception e2) {
					e2.printStackTrace();
				}
			}
		}
	}

	public List findCvList(Cv cv) {
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		if (cv != null) {
			sql.append(" select distinct a.cv_id from cv_contact_detail a where 1=1  and a.is_delete = 0  ");
			if (StringUtils.isNotEmpty(cv.getMobile())) {
				sql.append(" and  a.nbr like  ? ");
				values.add("%" + cv.getMobile() + "%");
			}
			if (StringUtils.isNotEmpty(cv.getEmail())) {
				if (StringUtils.isEmpty(cv.getMobile())) {
					sql.append(" and  a.nbr like ?  ");
					values.add("%" + cv.getEmail() + "%");
				} else {
					sql.append(" or a.nbr like ?  ");
					values.add("%" + cv.getEmail() + "%");
				}

			}
			if (StringUtils.isEmpty(cv.getMobile()) && StringUtils.isEmpty(cv.getEmail())) {
				return new ArrayList();
			}

		}
		return this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
	}

	@Override
	public boolean hasAccountPn(Integer cvId) {
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select 1  from contract_job_cv_recom a ,contract_job_cv_offer b where  b.recom_id = a.id and a.cv_id = ? ");
		values.add(cvId);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			return true;// 有Offfer
		}
		return false;
	}

	@Override
	public Page findCvDelPage(CvQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select a.id,a.cv_name cvName,a.cv_name_en cvNameEn,a.is_delete isDelete,delete_dt deleteDt,a.integrity,a.modifier,a.modified_dt modifiedDt ");
		sql.append("  from cv a  ");
		sql.append(" where 1=1 ");
		sql.append(" and (a.integrity = 9 or  a.integrity = -1 or a.is_delete = 1 )");
		if (isNotEmpty(query.getId())) {
			// 多个Id 一起查询
			sql.append(" and  find_in_set(a.id,?) ");
			values.add(query.getId());
		}
		// 中英文名
		if (isNotEmpty(query.getBothName())) {
			sql.append(" and (a.cv_name like ? or  a.cv_name_en like ? )");
			values.add("%" + query.getBothName() + "%");
			values.add("%" + query.getBothName() + "%");
		}
		if (isNotEmpty(query.getIntegrity())) {
			sql.append(" and a.integrity = ? ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getIsDelete())) {
			sql.append(" and a.is_delete= ?  ");
			values.add(query.getIsDelete());
		}
		sql.append(" ) cvdel where 1=1");
		sql.append("  order by cvdel.modifiedDt desc ");

		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	public Page findCvMergePage(CvQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select distinct * from ( ");
		sql.append(" select a.id,if(b.abbr_name <> '',b.abbr_name,a.company) company ,a.cv_name cvName,a.cv_name_en cvNameEn,a.industry,e.parent_id industryParent, a.function ,f.parent_id functionParent,a.integrity,a.mobile,a.email,a.tel,a.creator,a.create_dt createDt,a.modifier,a.modified_dt modifiedDt ");
		sql.append("  from cv a  ");
		sql.append(" left  join (select  a1.cv_name cvName from cv a1 where a1.is_delete = 0 and a1.integrity = '8' ) a2 on a.cv_name  = a2.cvName ");
		sql.append(" left  join (select  a1.cv_name_en cvNameEn from cv a1 where a1.is_delete = 0 and a1.integrity = '8' ) a3 on a.cv_name_en = a3.cvNameEn ");
		sql.append(" left join company b on b.id = a.company_id ");
		// 一级行业
		sql.append(" left join system_dic e on e.id = a.industry ");
		// 一级职能
		sql.append(" left join system_dic f on f.id = a.function ");
		// where a1.is_delete = 0 and a1.nbr <> ''
		/*
		 * sql.append(" inner join ("); sql.append(
		 * " select a.id, a.cv_id, a.nbr from cv_contact_detail a,( select a1.nbr from cv_contact_detail a1 group by a1.nbr having count(a1.nbr) > 1 ) b where a.nbr = b.nbr and a.is_delete = 0 and a.nbr <> ''  order by a.nbr "
		 * ); sql.append("  ) c  on c.cv_id = a.id    ");
		 */
		// sql.append(" "); //排除简历完整度 不能处理 和已经处理的.
		sql.append(" where 1=1  and ifnull(a.integrity,' ') not in ('7','10','11') ");
		sql.append(" and a.is_delete = 0 group by a.id ");
		// sql.append(" and (a.integrity = 9 or  a.integrity = -1 or a.is_delete = 1 )");
		sql.append(" ) cvmerge where 1=1   ");
		if (isNotEmpty(query.getId())) {
			sql.append(" and cvmerge.id = ?  ");
			values.add(query.getId());
		}
		// 中英文名
		if (isNotEmpty(query.getBothName())) {
			sql.append(" and (cvmerge.cvName like ? or  cvmerge.cvNameEn like ? )");
			values.add("%" + query.getBothName() + "%");
			values.add("%" + query.getBothName() + "%");
		}
		// 联系方式
		if (isNotEmpty(query.getContactDetails())) {
			sql.append(" and exists ( select 1 from cv_contact_detail a1 where   a1.is_delete = 0 and a1.cv_id=cvmerge.id and a1.nbr like ?  )");
			values.add("%" + query.getContactDetails() + "%");
		}
		if (isNotEmpty(query.getIntegrity())) {
			sql.append(" and cvmerge.integrity = ?  ");
			values.add(query.getIntegrity());
		}
		if (isNotEmpty(query.getSortColumns())) {
			sql.append(" order by cvmerge.").append(query.getSortColumns());
		} else {
			sql.append(" order by   cvmerge.cvName  ");
		}
		// sql.append("  order by cvmerge.nbr,cvmerge.id  ");
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}

	@Override
	@Transactional
	public synchronized void cvMerger(String id, String cvIds) {
		Cv cv = (Cv) this.getById(Integer.valueOf(id));
		String[] cvIdArr = cvIds.split(",");
		for (String cvId : cvIdArr) {
			if (StringUtils.isNotEmpty(cvId)) {
				Cv c = this.getById(Integer.valueOf(cvId));
				try {
					if (cv != null && c != null) {
						BeanUtils.copyPropertiesToNull(c, cv);
					}
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
			}
			this.insertCvMerger(id, cv.getCvName(), cvIds); // 插入记录
			cv.setIntegrity("10");
			this.update(cv); // 合并基本信息
			this.prepareCall(id, cvIds); // 调用存储过程
		}

	}

	/**
	 * 记录的合并表
	 * 
	 * @param cvId
	 * @param cvIds
	 */
	public void insertCvMerger(String cvId, String cvName, String cvIds) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append("insert into cv_merge_log values (null,?,?,?,?,?) ");
		values.add(cvId);
		values.add(cvName);
		values.add(cvIds);
		values.add("alvin.liu");
		values.add(new Date());
		this.getJdbcDao().save(sql.toString(), values.toArray());

	}

	/**
	 * 调用存储过程
	 * 
	 * @param cvId
	 * @param cvIds
	 */
	public void prepareCall(final String cvId, final String cvIds) {
		final String callProcedureSql = "{call cv_merge(?,?)}"; // 定义sql
		List<SqlParameter> params = new ArrayList<SqlParameter>();
		params.add(new SqlParameter("cvId", Types.INTEGER));
		params.add(new SqlParameter("cvIds", Types.VARCHAR));

		Map<String, Object> outValues = this.getJdbcDao().getJdbcTemplate().call(new CallableStatementCreator() {

			@Override
			public CallableStatement createCallableStatement(Connection conn) throws SQLException {
				CallableStatement cstmt = conn.prepareCall(callProcedureSql);
				cstmt.setInt(1, Integer.valueOf(cvId));
				cstmt.setString(2, cvIds);

				return cstmt;
			}
		}, params);
	}

	@Override
	public Page findSameOfDelCvPage(CvQuery cvQuery) {
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ( ");
		sql.append(" select a.id,a.cv_name cvName,a.cv_name_en cvNameEn,a.company,a.integrity,a.modifier,a.modified_dt modifiedDt ");
		sql.append(" from cv a where 1=1 and a.id <> ?  ");
		values.add(cvQuery.getId());
		if (isNotEmpty(cvQuery.getCvName()) && isNotEmpty(cvQuery.getCvNameEn())) {
			sql.append(" and (a.cv_name = ? or a.cv_name_en = ? )");
			values.add(cvQuery.getCvName());
			values.add(cvQuery.getCvNameEn());
		} else if (isNotEmpty(cvQuery.getCvName()) && !isNotEmpty(cvQuery.getCvNameEn())) {
			sql.append(" and a.cv_name = ? ");
			values.add(cvQuery.getCvName());
		} else if (!isNotEmpty(cvQuery.getCvName()) && isNotEmpty(cvQuery.getCvNameEn())) {
			sql.append(" and a.cv_name_en = ? ) ");
			values.add(cvQuery.getCvNameEn());
		}
		sql.append(" ) t where 1=1 ");
		return super.getJdbcDao().createPage(sql.toString(), values.toArray(), new MapObjectRowMapper(), cvQuery.getPageSize(), cvQuery.getPageNumber());
	}

	/**
	 * 完善度处于“申请删除”或者“SC要求删除”不允许添加到SL或BD公司中。
	 */
	@Override
	public boolean checkCvIntegrity(Integer id) {
		List integrityList = new ArrayList();
		integrityList.add("-1"); // 申请删除
		integrityList.add("9"); // SC要求删除
		List values = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql.append(" select a.id,a.integrity  ");
		sql.append(" from cv a where 1=1 and a.id =  ?  ");
		values.add(id);
		List l = this.getJdbcDao().findByQuery(sql.toString(), values.toArray(), new MapObjectRowMapper());
		if (l != null && l.size() > 0) {
			Map m = (Map) l.get(0);
			String integrity = (String) m.get("integrity");
			if (isNotEmpty(integrity)) {
				if (integrityList.contains(integrity)) {
					return false;
				}
			}

		}
		return true;
	}

	@Override
	public void recoverCv(String ids) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" update cv a set a.integrity = null ,a.is_delete = 0 ,a.delete_dt = null where a.is_delete <> 0 and find_in_set(a.id,?) ");
		values.add(ids);
		this.getJdbcDao().update(sql.toString(), values.toArray());

	}

	@Override
	public void updateCompanyName(Integer companyId, String attrName) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" update cv a set a.company = ? where a.company_id = ?  ");
		values.add(attrName);
		values.add(companyId);
		this.getJdbcDao().update(sql.toString(), values.toArray());
	}
}
